Match Excel Function

The MATCH function looks for a specific value and returns its relative position in a given range of cells. The output is the first position found for the given value. Being a lookup and reference function, it works for both an exact and approximate match. For example, if the range A11:A15 consists of the numbers 2, 9, 8, 14, 32, the formula “MATCH(8,A11:A15,0)” returns 3. This is because the number 8 is at the third position.

In simple words, the MATCH formula is given as follows:

“MATCH(value to be searched, array, exact or approximate match )”

The syntax of the function is shown in the following image:

The function accepts the following arguments:

• Lookup_value: This is the value to be searched in the “lookup_array.”
• Lookup_array: This is the array or range of cells where the “lookup_value” is to be searched.
• Match_type: This takes the values 1, 0, or -1 depending on the type of match.

For instance, you may want to search a specific word (lookup_value) in the dictionary (lookup_array).

The arguments “lookup_value” and “lookup_array” are mandatory, while “match_type” is optional.

The Values of “Match_Type"

The “match_type” can take any of the following values:

Positive one (1): The function looks for the largest value in the “lookup_array,” which is less than or equal to the “lookup_value.” The data is arranged in alphabetical (A to Z) or ascending order and an approximate match is returned.

Zero (0): The function looks for an exact match of the “lookup_value” in the “lookup_array.” The data is not required to be arranged.

Negative one (-1): The function looks for the smallest value in the “lookup_array,” which is greater than or equal to the “lookup_value.” The data is arranged in reverse order of alphabets (Z to A) or descending order and an approximate match is returned.

Note: The default value of “match_type” is 1.

Let us understand the working of the MATCH formula with the help of examples.

Example #1–Exact Match

The succeeding table shows the serial number (S.N.), name, and department of ten employees in an organization. We want to find the position of the employee “Tanuj.”

We apply the following formula.

“=MATCH(F4,$B$4:$B$13,0)”

The “match_type” is set at 0 to return the exact position of “Tanuj” (lookup_value) from the range $B$4:$B$13 (lookup_array). The output is 1.

Example #2–Approximate Match

The succeeding list shows the values from 100 to 1000. We want to find the approximate position of the value 525.

We apply the following formula.

“=MATCH(E19,B19:B28,1)”

The “match_type” is set at 1 to return the approximate match of 525 (lookup_value) from the range B19:B28 (lookup_array).

The MATCH function looks for the largest value (500), which is less than 525 in the given array. Hence, the output is 5.

Example #3–Wildcard Character (Partial Match)

The MATCH function supports the usage of wildcard characters (? and *) in the “lookup_value” argument. Let us consider an example of the same.

The succeeding list shows ten IDs of the various employees of an organization. We want to find the position of the ID ending with 105.

We apply the following formula.

“=MATCH("*"&E33,$B$33:$B$42,0)”

The wildcard characters are used for partial matches and the “match_type” is set at zero. The output is 5. This implies that the ID at the fifth position is ending with 105.

Example 4–INDEX MATCH

The MATCH and INDEX function are used together to look up a value in the table from right to left.

The succeeding table shows the serial number (S.N.), name, and department of ten employees in an organization. We want to find the serial number of the employee “Tanuj.”

We apply the following formula.

“=INDEX(B46:D56,MATCH(G48,C46:C56,0),1)”

The MATCH function searches for the exact word “Tanuj” in the range C46:C56 and returns 2. The output 2 is supplied as the row number to the INDEX function. The INDEX function returns the value from the second row and first column of the range B46:D56.

The output of the formula is 1. This implies that the serial number of “Tanuj” is 1.

The following image shows the output when the “lookup_value” is “Tanujh.” Since “Tanujh” could not be found in column B, the outcome is “#N/A” error.

• It is not case-sensitive which implies that it does not distinguish between the uppercase and lowercase letters.
• It returns the relative position of the “lookup_value” in the “lookup_array.”
• It works with one-dimensional ranges or arrays which can be either vertical or horizontal.
• If there are multiple occurrences of the “lookup_value” in the “lookup_array,” it returns the position of the first exact match.
• If the “lookup_value” is in text form, the wildcard characters like a question mark (?) and asterisk (*) can be used for partial matches.
• It returns the “#N/A” error if it is unable to find the “lookup_value” in the “lookup_array.”